﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace TSGuards
{
    class clsDB
    {
        public OleDbConnection MAconn;
        public OleDbCommand MAcmd;

        public void Log(string logMessage, TextWriter w)
        {
            w.Write("\r\n{0} {1}", DateTime.Now.ToLongTimeString(),
                DateTime.Now.ToShortDateString());
            w.Write("\t:{0}", logMessage);
        }

        public void writeLog(string theMessage)
        {
            using (StreamWriter w = File.AppendText("log.txt"))
            {
                Log(theMessage,w);
            }
        }

        public void TransferUserSchedule()
        {
            StringBuilder bulk = new StringBuilder();
            string connectionstring = string.Format(Properties.Settings.Default.mdbConnectionString, Properties.Settings.Default.mdbfilename);

            string MysqlConString = string.Format(Properties.Settings.Default.mysqlConnectionString, new Object[]{
                Properties.Settings.Default.mysqlserver,
                Properties.Settings.Default.mysqldatabase,
                Properties.Settings.Default.mysqluser,
                Properties.Settings.Default.mysqlpassword
            });

            string sel = "select * from UserUsedSClasses";
            string mysqlinsert = "";
            // DataSet _dataset = new DataSet();
            DataTable _datatable = new DataTable();
            try
            {
                using (OleDbConnection _oledbconnection = new OleDbConnection(connectionstring))
                {
                    OleDbCommand _command = new OleDbCommand(sel, _oledbconnection);
                    _oledbconnection.Open();
                    OleDbDataAdapter _dataadapter = new OleDbDataAdapter(_command);
                    _dataadapter.Fill(_datatable);//--> untuk di lempar ke mysql

                    //disconnect from database
                    if (_oledbconnection != null)
                    {
                        if (_oledbconnection.State == ConnectionState.Open) _oledbconnection.Close();
                        _oledbconnection.Dispose();
                    }

                    //open connection to mysql
                    MySqlConnection _mysqlconn = new MySqlConnection(MysqlConString);
                    _mysqlconn.Open();

                    mysqlinsert = "delete from am_ts_userusedsclasses;";
                    MySqlCommand _mysqlcmddel = new MySqlCommand(mysqlinsert, _mysqlconn);
                    _mysqlcmddel.ExecuteNonQuery();

                    foreach (DataRow _datarow in _datatable.Rows)
                    {
                        try
                        {
                            mysqlinsert = "insert into am_ts_userusedsclasses (userid,schid) values(@userid,@schid);";
                            MySqlCommand _mysqlcmd = new MySqlCommand(mysqlinsert, _mysqlconn);
                            _mysqlcmd.Parameters.AddWithValue("@userid", _datarow[0].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@schid", _datarow[1].ToString());
                            _mysqlcmd.ExecuteNonQuery();
                            writeLog("TransferUserSchedule userid:" + _datarow[0].ToString() + " ; schid:" + _datarow[1].ToString());

                        }
                        catch (Exception exc)
                        {
                            MessageBox.Show(exc.Message);
                        }

                    }
                    _mysqlconn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Occured : " + ex.Message);
                throw;
            }
        }


        public void TransferSchedule()
        {
            StringBuilder bulk = new StringBuilder();
            string connectionstring = string.Format(Properties.Settings.Default.mdbConnectionString, Properties.Settings.Default.mdbfilename);

            string MysqlConString = string.Format(Properties.Settings.Default.mysqlConnectionString, new Object[]{
                Properties.Settings.Default.mysqlserver,
                Properties.Settings.Default.mysqldatabase,
                Properties.Settings.Default.mysqluser,
                Properties.Settings.Default.mysqlpassword
            });

            string sel = "SELECT * FROM SCHCLASS;";
            string mysqlinsert = "";
            // DataSet _dataset = new DataSet();
            DataTable _datatable = new DataTable();
            try
            {
                using (OleDbConnection _oledbconnection = new OleDbConnection(connectionstring))
                {
                    OleDbCommand _command = new OleDbCommand(sel, _oledbconnection);
                    _oledbconnection.Open();
                    OleDbDataAdapter _dataadapter = new OleDbDataAdapter(_command);
                    _dataadapter.Fill(_datatable);//--> untuk di lempar ke mysql


                    /*try
                    {
                        _mysqlcmd = _mysqlconn.CreateCommand();
                        _mysqlcmd.CommandText = "insert into am_ts_userinfo";
                    }
                    catch (Exception)
                    {
                        
                        throw;
                    }*/


                    //disconnect from database
                    if (_oledbconnection != null)
                    {
                        if (_oledbconnection.State == ConnectionState.Open) _oledbconnection.Close();
                        _oledbconnection.Dispose();
                    }

                    //open connection to mysql
                    MySqlConnection _mysqlconn = new MySqlConnection(MysqlConString);
                    _mysqlconn.Open();

                    mysqlinsert = "delete from am_ts_schclass;";
                    MySqlCommand _mysqlcmddel = new MySqlCommand(mysqlinsert, _mysqlconn);
                    _mysqlcmddel.ExecuteNonQuery();

                    foreach (DataRow _datarow in _datatable.Rows)
                    {
                        try
                        {
                            //MessageBox.Show("start: " + _datarow[2].ToString() + "| end: " + _datarow[3].ToString());

                            mysqlinsert = "insert into am_ts_schclass (schclassid,schname,starttime,endtime,lateminutes,earlyminutes,checkin,checkout,checkintime1,checkintime2,checkouttime1,checkouttime2) VALUES (@schclassid,@schname,STR_TO_DATE(@starttime,'%e/%c/%Y %k:%i:%S'),STR_TO_DATE(@endtime,'%e/%c/%Y %k:%i:%S'),@lateminutes,@earlyminutes,@checkin,@checkout,STR_TO_DATE(@checkintime1,'%e/%c/%Y %k:%i:%S'),STR_TO_DATE(@checkintime2,'%e/%c/%Y %k:%i:%S'),STR_TO_DATE(@checkouttime1,'%e/%c/%Y %k:%i:%S'),STR_TO_DATE(@checkouttime2,'%e/%c/%Y %k:%i:%S'));";
                            //mysqlinsert = "insert into am_ts_schclass (schclassid,schname,starttime,endtime,lateminutes,earlyminutes,checkin,checkout,checkintime1,checkintime2,checkouttime1,checkouttime2) VALUES (@schclassid,@schname,STR_TO_DATE(@starttime,'%c/%e/%Y %r'),STR_TO_DATE(@endtime,'%c/%e/%Y %r'),@lateminutes,@earlyminutes,@checkin,@checkout,STR_TO_DATE(@checkintime1,'%c/%e/%Y %r'),STR_TO_DATE(@checkintime2,'%c/%e/%Y %r'),STR_TO_DATE(@checkouttime1,'%c/%e/%Y %r'),STR_TO_DATE(@checkouttime2,'%c/%e/%Y %r'));";
                            MySqlCommand _mysqlcmd = new MySqlCommand(mysqlinsert, _mysqlconn);
                            _mysqlcmd.Parameters.AddWithValue("@schclassid", _datarow[0].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@schname", _datarow[1].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@starttime", _datarow[2].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@endtime", _datarow[3].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@lateminutes", _datarow[4].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@earlyminutes", _datarow[5].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@checkin", _datarow[6].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@checkout", _datarow[7].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@checkintime1", _datarow[8].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@checkintime2", _datarow[9].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@checkouttime1", _datarow[10].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@checkouttime2", _datarow[11].ToString());
                            _mysqlcmd.ExecuteNonQuery();
                            writeLog(mysqlinsert);
                            writeLog("TransferSchedule schclassid:" + _datarow[0].ToString() + 
                                " ; schname:" + _datarow[1].ToString() +
                                " ; starttime:" + _datarow[2].ToString() +
                                " ; endtime:" + _datarow[3].ToString() +
                                " ; lateminutes:" + _datarow[4].ToString() +
                                " ; earlyminutes:" + _datarow[5].ToString() +
                                " ; checkin:" + _datarow[6].ToString() +
                                " ; checkout:" + _datarow[7].ToString() +
                                " ; checkintime1:" + _datarow[8].ToString() +
                                " ; checkintime2:" + _datarow[9].ToString() +
                                " ; checkouttime1:" + _datarow[10].ToString() +
                                " ; checkouttime2:" + _datarow[11].ToString()  
                                );

                        }
                        catch (Exception exc)
                        {
                            MessageBox.Show(exc.Message);
                        }

                    }
                    _mysqlconn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Occured : " + ex.Message);
                throw;
            }
        }

        public void TransferMasterUserData()
        {
            StringBuilder bulk = new StringBuilder();
            string connectionstring = string.Format(Properties.Settings.Default.mdbConnectionString, Properties.Settings.Default.mdbfilename);

            string MysqlConString = string.Format(Properties.Settings.Default.mysqlConnectionString,new Object[]{
                Properties.Settings.Default.mysqlserver,
                Properties.Settings.Default.mysqldatabase,
                Properties.Settings.Default.mysqluser,
                Properties.Settings.Default.mysqlpassword
            });

            string sel = "select * from userinfo";
            string mysqlinsert = "";
            // DataSet _dataset = new DataSet();
            DataTable _datatable = new DataTable();
            try
            {
                using (OleDbConnection _oledbconnection = new OleDbConnection(connectionstring))
                {
                    OleDbCommand _command = new OleDbCommand(sel, _oledbconnection);
                    _oledbconnection.Open();
                    OleDbDataAdapter _dataadapter = new OleDbDataAdapter(_command);
                    _dataadapter.Fill(_datatable);//--> untuk di lempar ke mysql


                    /*try
                    {
                        _mysqlcmd = _mysqlconn.CreateCommand();
                        _mysqlcmd.CommandText = "insert into am_ts_userinfo";
                    }
                    catch (Exception)
                    {
                        
                        throw;
                    }*/


                    //disconnect from database
                    if (_oledbconnection != null)
                    {
                        if (_oledbconnection.State == ConnectionState.Open) _oledbconnection.Close();
                        _oledbconnection.Dispose();
                    }

                    //open connection to mysql
                    MySqlConnection _mysqlconn = new MySqlConnection(MysqlConString);
                    _mysqlconn.Open();

                    mysqlinsert = "delete from am_ts_userinfo;";
                    MySqlCommand _mysqlcmddel = new MySqlCommand(mysqlinsert, _mysqlconn);
                    _mysqlcmddel.ExecuteNonQuery();

                    foreach (DataRow _datarow in _datatable.Rows)
                    {
                        try
                        {
                            mysqlinsert = "insert into am_ts_userinfo (userid,badgenumber,name) values(@id,@badge,@name);";
                            MySqlCommand _mysqlcmd = new MySqlCommand(mysqlinsert, _mysqlconn);
                            _mysqlcmd.Parameters.AddWithValue("@id", _datarow[0].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@badge", _datarow[1].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@name", _datarow[3].ToString());
                            _mysqlcmd.ExecuteNonQuery();
                            writeLog(mysqlinsert);
                            writeLog("TransferMasterUserData id:" + _datarow[0].ToString() + " ; badge:" + _datarow[1].ToString() +  " ; name:" + _datarow[3].ToString());

                        }
                        catch (Exception exc)
                        {
                            MessageBox.Show(exc.Message);
                        }

                    }
                    _mysqlconn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Occured : " + ex.Message);
                throw;
            }
        }

        public void TransferCheckTrxData()
        {
            StringBuilder bulk = new StringBuilder();
            string connectionstring = string.Format(Properties.Settings.Default.mdbConnectionString, Properties.Settings.Default.mdbfilename);

            string MysqlConString = string.Format(Properties.Settings.Default.mysqlConnectionString, new Object[]{
                Properties.Settings.Default.mysqlserver,
                Properties.Settings.Default.mysqldatabase,
                Properties.Settings.Default.mysqluser,
                Properties.Settings.Default.mysqlpassword
            });

            string sel = "select * from checkinout  where flag is null";
            string mysqlinsert = "";
            // DataSet _dataset = new DataSet();
            DataTable _datatable = new DataTable();
            try
            {
                using (OleDbConnection _oledbconnection = new OleDbConnection(connectionstring))
                {
                    OleDbCommand _command = new OleDbCommand(sel, _oledbconnection);
                    _oledbconnection.Open();
                    OleDbDataAdapter _dataadapter = new OleDbDataAdapter(_command);
                    _dataadapter.Fill(_datatable);//--> untuk di lempar ke mysql


                    //disconnect from database
                    if (_oledbconnection != null)
                    {
                        if (_oledbconnection.State == ConnectionState.Open) _oledbconnection.Close();
                        _oledbconnection.Dispose();
                    }

                    //open connection to mysql
                    MySqlConnection _mysqlconn = new MySqlConnection(MysqlConString);
                    _mysqlconn.Open();

                    foreach (DataRow _datarow in _datatable.Rows)
                    {
                        try
                        {
                            //MessageBox.Show(_datarow[1].ToString());
                            mysqlinsert = "insert into am_ts_checkinout (userid,checktime,checktype,verifycode,sensorid,workcode,sn) values(@_userid,STR_TO_DATE(@_checktime, '%e/%c/%Y %k:%i:%S'),@_checktype,@_verifycode,@_sensorid,@_workcode,@_sn);";
                            MySqlCommand _mysqlcmd = new MySqlCommand(mysqlinsert, _mysqlconn);
                            _mysqlcmd.Parameters.AddWithValue("@_userid", _datarow[0].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@_checktime", _datarow[1].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@_checktype", _datarow[2].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@_verifycode", _datarow[3].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@_sensorid", _datarow[4].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@_workcode", _datarow[5].ToString());
                            _mysqlcmd.Parameters.AddWithValue("@_sn", _datarow[6].ToString());
                            writeLog(mysqlinsert);
                            _mysqlcmd.ExecuteNonQuery();
                            writeLog("TransferCheckTrxData id:" + _datarow[0].ToString() + " ; checktime:" + _datarow[1].ToString() + " ; checktype:" + _datarow[2].ToString() + " ; verifycode:" + _datarow[3].ToString() + " ; sensorid:" + _datarow[4].ToString() + " ; workcode:" + _datarow[5].ToString() + " ; sn:" + _datarow[6].ToString());
                        }
                        catch (Exception exc)
                        {
                            MessageBox.Show(exc.Message);
                        }

                    }

                    _mysqlconn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("error ini : " + ex.Message);
                throw;
            }
//--------------------
            ////update access set flag = y
            //string updateflag = "update checkinout set flag = \"Y\" where flag is null";

            //try
            //{
            //    using (OleDbConnection _oledbconn = new OleDbConnection(connectionstring))
            //    {
            //        OleDbCommand _command = new OleDbCommand(updateflag, _oledbconn);
            //        _oledbconn.Open();
            //        _command.ExecuteNonQuery();

            //        //disconnect from database
            //        if (_oledbconn != null)
            //        {
            //            if (_oledbconn.State == ConnectionState.Open) _oledbconn.Close();
            //            _oledbconn.Dispose();
            //        }


            //    }
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show("error update : " + ex.Message);
            //    throw;
            //}

//-------------------


        }

        public bool dbConnectMDB(string filename)
        {
            bool conn_succ = false;
            if (MAconn != null && MAconn.State == ConnectionState.Open)
            {
                MAconn.Close();
                MAcmd.Dispose();
            }
            try
            {
                MAconn = new OleDbConnection();
                MAconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filename;
                MAconn.Open();
                MAcmd = MAconn.CreateCommand();
                MessageBox.Show("Connect successful!", "Connect command");
                conn_succ = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return conn_succ;
            }
            return conn_succ;
        }

        public bool dbDisconnectMDB()
        {
            bool conn_closed = false;
            if (MAconn != null)
            {
                if (MAconn.State == ConnectionState.Open) MAconn.Close();
                MAconn.Dispose();
                conn_closed = true;
            }
            return conn_closed;
        }
    }
}
